package org.casper.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.casper.bean.User;
import org.casper.util.DBUtil;

public class UserDao {
	
	
	/**
	 * 分页查询用户
	 * @Author Casper
	 * Create_time:2015年10月18日 下午1:43:23
	 * description:
	 */
	public ArrayList<User> selectUserByPage(int offset,int rows){
		ArrayList<User> allUsers = new ArrayList<User>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		conn = DBUtil.getConn();
		try {
			pstmt = conn.prepareStatement("select * from t_user limit ?,?");
			pstmt.setInt(1, offset);
			pstmt.setInt(2,rows);
			rs = pstmt.executeQuery();
			while(rs.next()){
				allUsers.add(new User(rs.getInt("id"),rs.getString("username"),rs.getString("userpass")));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(conn, pstmt, rs);
		}
		return allUsers;
	}
	
	
	
	/**
	 * 获取t_user表的总记录数
	 * @Author Casper
	 * Create_time:2015年10月18日 下午1:34:29
	 * description:
	 */
	public int selectCount(){
		int count = 0;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		conn = DBUtil.getConn();
		try {
			pstmt = conn.prepareStatement("select count(id) from t_user");
			rs = pstmt.executeQuery();
			while(rs.next()){
				count = rs.getInt("count(id)");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(conn, pstmt, rs);
		}
		return count;
	}
	
	/**
	 * 添加一个用户
	 * @Author Casper
	 * Create_time:2015年10月18日 下午1:41:32
	 * description:
	 */
	public int addUser(User newUser){
		int resultCode = 1;
		Connection conn = null;
		PreparedStatement pstmt = null;
		conn = DBUtil.getConn();
		try {
			pstmt = conn.prepareStatement("insert into t_user(username,userpass) values(?,?)");
			pstmt.setString(1, newUser.getUsername());
			pstmt.setString(2, newUser.getUserpass());
			resultCode = pstmt.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(conn, pstmt);
		}
		return resultCode;
	}
	
	/**
	 * 查询全部的用户
	 * @Author Casper
	 * Create_time:2015年10月18日 下午1:41:47
	 * description:
	 */
	public ArrayList<User> selectAllUsers(){
		ArrayList<User> allUsers = new ArrayList<User>();
		Connection conn = null;
		PreparedStatement pstmt =null;
		ResultSet rs =null;
		conn = DBUtil.getConn();
		try {
			pstmt = conn.prepareStatement("select id,username,userpass from t_user");
			rs = pstmt.executeQuery();
			User user = null;
			while(rs.next()){
				user = new User(rs.getInt("id"), rs.getString("username"), rs.getString("userpass"));
				allUsers.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}finally{
			DBUtil.closeAll(conn, pstmt, rs);
		}
		return allUsers;
	}
	
	
	
	/**
	 * 仅仅作为测试使用
	 * @Author Casper
	 * Create_time:2015年10月18日 下午12:12:31
	 * description:
	 */
	public static void main(String[] args) {
		UserDao userDao = new UserDao();
		//userDao.addUser(new User("宝马", "123456"));
		for(User user:userDao.selectAllUsers()){
			//System.out.println(user);
		}
		//System.out.println(userDao.selectCount());
		for(User u:userDao.selectUserByPage(10, 10)){
			System.out.println(u);
		}
	}
}
